package uniLearn.control;

import java.io.File;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;

import org.apache.commons.fileupload.FileItem;
import org.apache.commons.io.FileUtils;
import org.apache.commons.io.FilenameUtils;

import uniLearn.data.SubmitAssignment;
import uniLearn.data.SubmitStatus;
import uniLearn.data.Users;

public class SubmitAssignmentControl extends DataControl {
	//define
	//SQL Command
	private static String SQL_SELECT_SUBMIT_ASSIGNMENT = "SELECT `ag_id`, `fileName`, `submitTime`, `status` FROM `submit_assignment` WHERE `ac_id` = ? AND `submitBy` = ?;";
	private static String SQL_SELECT_FILEPATH = "SELECT `fileName` FROM `submit_assignment` WHERE `ac_id` = ? AND `submitBy` = ?;";
	private static String SQL_UPDATA_SUBMIT_ASSIGNMENT = "INSERT INTO `submit_assignment` (`ac_id`, `submitBy`, `ag_id`, `fileName`, `submitTime`, `status`) VALUES (?, ?, ?, ?, NOW(), ?)"+ 
			"ON DUPLICATE KEY UPDATE `fileName`= VALUES(`fileName`), `submitTime`= VALUES(`submitTime`),  `status`= VALUES(`status`);";
	private static String SQL_MARK_STATUS = "UPDATE `submit_assignment` SET `status` = ? WHERE `ac_id` = ? AND `submitBy` = ?;";
	//private static String SQL_SELECT_WHO_SUBMIT_ASSIGNMENT = "SELECT `submitBy`, `ag_id`, `fileName`, `submitTime`, `status` FROM `submit_assignment` WHERE `ac_id` = ?;";	
	
	//path of director to storage all the submit assignment
	private static String SA_DIR_PATH = ROOT_DIR_PATH + "\\submit_assignment";
	
	public String getDirPath() {
		return ROOT_DIR_PATH;
	}

	public void uploadFile(FileItem uploadItem, int acId, String submitBy) throws Exception {
		uploadFile(uploadItem, acId, submitBy, -1);
	}
	
	public void uploadFile(FileItem uploadItem, int acId, String submitBy, int agId) throws Exception {
		if(acId < 1 || checkLength(submitBy, Users.ID_MAX_LENGTH))
			throw new Exception("Incorrect Assignment ID or User ID");
		
		if(uploadItem.isFormField())
			throw new Exception("Incorrect Item Input");
		
		String fileName = uploadItem.getName();
		if(checkLength(fileName))
			throw new Exception("Incorrect Item Input"); // incorrect file upload
		//rename the upload file to be the name of login user
		fileName = FilenameUtils.getName(submitBy+ "." + FilenameUtils.getExtension(fileName));
		
		//Create the folder of the assignment if it is not exist
		File fileObj = new File(SA_DIR_PATH + "\\"+ acId);
		FileUtils.forceMkdir(fileObj);
		
		//Write the file
		fileObj = new File(fileObj, fileName);
		//Check the old is exists or not. If exists, delete the older one.
		if(fileObj.exists())
			if(!fileObj.delete())
				throw new Exception("Can't replace the file uploaded before"); //Can't delete the old file
		
		//write the file to storage
		uploadItem.write(fileObj);
		
		//Update the Submit Assignment Status
		this.startConnection();
		PreparedStatement preStmt = this.dbCon.preStmt(SQL_UPDATA_SUBMIT_ASSIGNMENT);
		//set parameter
		preStmt.setInt(1, acId);
		preStmt.setString(2, submitBy);
		preStmt.setInt(3, agId);
		preStmt.setString(4, fileObj.getName());
		preStmt.setString(5, SubmitStatus.unmarked.toString());
		
		int result = preStmt.executeUpdate();
		preStmt.close();
		
		if(result < 1)
			throw new Exception("Can't update submit file status");
	}
	
	public String getDownloadPath(int acId, String submitBy) throws Exception {
		if(acId < 1 || checkLength(submitBy, Users.ID_MAX_LENGTH))
			throw new Exception("Incorrect Assignment ID or User ID");
		
		String filePath = SA_DIR_PATH+ "\\"+ acId+ "\\";
		// Update the Submit Assignment Status
		this.startConnection();
		PreparedStatement preStmt = this.dbCon.preStmt(SQL_SELECT_FILEPATH);
		// set parameter
		preStmt.setInt(1, acId);
		preStmt.setString(2, submitBy);
		
		ResultSet rs = preStmt.executeQuery();
		if(rs.next()) {
			filePath += rs.getString(1);
		} else {
			rs.close();
			preStmt.close();
			throw new Exception("Can't find the file");
		}
		
		rs.close();
		preStmt.close();
		return filePath;
	}

	public void markStatus(int acId, String submitBy, SubmitStatus status) throws Exception {
		if(acId < 1 || checkLength(submitBy, Users.ID_MAX_LENGTH))
			throw new Exception("Incorrect Assignment ID or User ID");

		// Update the Submit Assignment Status
		this.startConnection();
		PreparedStatement preStmt = this.dbCon.preStmt(SQL_MARK_STATUS);
		// set parameter
		preStmt.setString(1, status.toString());
		preStmt.setInt(2, acId);
		preStmt.setString(3, submitBy);

		int result = preStmt.executeUpdate();
		preStmt.close();

		if (result < 1)
			throw new Exception("Can't update Submit Assignment status");
	}
	
	public SubmitAssignment getSubmited(int acId, String submitBy) throws Exception{
		if(acId < 1 || checkLength(submitBy, Users.ID_MAX_LENGTH))
			throw new Exception("Incorrect Assignment ID or User ID");
		
		SubmitAssignment sa = null;
		
		//Get from database
		this.startConnection();
		PreparedStatement preStmt = this.dbCon.preStmt(SQL_SELECT_SUBMIT_ASSIGNMENT);
		//set parameter
		preStmt.setInt(1, acId);
		preStmt.setString(2, submitBy);
		//Run SQL
		ResultSet rs = preStmt.executeQuery();
		//Check the Result Set
		if(rs.next()) {
			int agId = rs.getInt("ag_id");
			String filename = rs.getString("filename");
			Date submitTime = new Date(rs.getTimestamp("submitTime").getTime());
			SubmitStatus status = SubmitStatus.fromString(rs.getString("status"));
			
			sa = new SubmitAssignment(acId, submitBy, agId);
			sa.setFileName(filename);
			sa.setSubmitTime(submitTime);
			sa.setSubmitStatus(status);
		}
		rs.close();
		preStmt.close();
		
		return sa;
	}
	
/*	public ArrayList<SubmitAssignment> getListofSubmited(int acId) throws Exception {
		if(acId < 1 )
			throw new Exception("Incorrect Assignment ID");
		ArrayList<SubmitAssignment> listSubmit = new ArrayList<SubmitAssignment>();
		
		//Start connection
		this.startConnection();
		PreparedStatement preStmt = this.dbCon.preStmt(SQL_SELECT_WHO_SUBMIT_ASSIGNMENT);
		//set parameter
		preStmt.setInt(1, acId);
		//Run SQL
		ResultSet rs = preStmt.executeQuery();
		while(rs.next()) {
			String submitBy = rs.getString("submitBy");
			int agId = rs.getInt("ag_id");
			String filename = rs.getString("filename");
			Date submitTime = rs.getDate("submitTime");
			SubmitStatus status = SubmitStatus.fromString(rs.getString("status"));
			
			SubmitAssignment sa = new SubmitAssignment(acId, submitBy, agId);
			sa.setFileName(filename);
			sa.setSubmitTime(submitTime);
			sa.setSubmitStatus(status);
			listSubmit.add(sa);
		}
		
		return listSubmit;
	}*/
}
